package org.example.面向过程;

import com.mysql.cj.jdbc.Driver;

import java.sql.*;
import java.util.Scanner;

public class Work {
    //1.通过JDBC向emp表录入一条数据,要求录入的职位、上司必须存在
    public static void a(){
        try {
            Class.forName(Driver.class.getName());
        }catch (ClassNotFoundException e){
            System.out.println("没有找到驱动！");
        }
        try {
            String sul = "jdbc:mysql://localhost:3306/bd2005?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
            String username = "root";
            String password = "a123456";
            Connection conn = DriverManager.getConnection(sul,username,password);
            String sql = "insert into emp values(1,'haha','CLERK',7788,'1997-05-05',1100,null,20,1)";
            PreparedStatement ps = conn.prepareStatement(sql);
            int r = ps.executeUpdate();
            System.out.println(r);
            ps.close();
            conn.close();
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }
    }
    //2.通过JDBC将emp表中奖金为Null的员工修改成0
    public static void b(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (ClassNotFoundException e){
            System.out.println("没有找到驱动！");
        }
        try {
            String sul = "jdbc:mysql://localhost:3306/bd2005?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
            String username = "root";
            String password = "a123456";
            Connection conn = DriverManager.getConnection(sul,username,password);
            String sql = "update emp set comm=0 where comm is null ";
            PreparedStatement ps = conn.prepareStatement(sql);
            int r = ps.executeUpdate();
            System.out.println(r);
            ps.close();
            conn.close();
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }
    }
    //3.通过JDBC将emp表中工资小于800的员工删除
    public static void c(){
        try {
            Class.forName(Driver.class.getName());
        }catch (ClassNotFoundException e){
            System.out.println("没有找到驱动！");
        }
        try {
            String sul = "jdbc:mysql://localhost:3306/bd2005?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
            String username = "root";
            String password = "a123456";
            Connection conn = DriverManager.getConnection(sul,username,password);
            String sql = "delete from emp where sal<800";
            PreparedStatement ps = conn.prepareStatement(sql);
            int r = ps.executeUpdate();
            System.out.println(r);
            ps.close();
            conn.close();
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }
    }
    //4.通过JDBC创建一个新部门，将刚录入的员工移入到新部门中
    public static void d(){
        try {
            Class.forName(Driver.class.getName());
        }catch (ClassNotFoundException e){
            System.out.println("没有找到驱动！");
        }
        try {
            String sul = "jdbc:mysql://localhost:3306/bd2005?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
            String username = "root";
            String password = "a123456";
            Connection conn = DriverManager.getConnection(sul,username,password);
            String sql1 = "insert into dept values (50,'游戏','timi')";
            String sql2 = "update emp set deptno=50 where empno=1";
            PreparedStatement ps1 = conn.prepareStatement(sql1);
            PreparedStatement ps2 = conn.prepareStatement(sql2);
            int r1 = ps1.executeUpdate();
            int r2 = ps2.executeUpdate();
            System.out.println(r1+"\n"+r2);
            ps2.close();
            ps1.close();
            conn.close();
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }

    }
    //5.通过JDBC将刚才新建部门删除，所属员工移入其他部门
    public static void e() {
        try {
            Class.forName(Driver.class.getName());
        }catch (ClassNotFoundException e){
            System.out.println("没有找到驱动！");
        }
        try {
            String sul = "jdbc:mysql://localhost:3306/bd2005?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
            String username = "root";
            String password = "a123456";
            Connection conn = DriverManager.getConnection(sul,username,password);
            String sql1 = "delete from dept where deptno=50";
            String sql2 = "update emp set deptno=20 where deptno=50";
            PreparedStatement ps1 = conn.prepareStatement(sql1);
            PreparedStatement ps2 = conn.prepareStatement(sql2);
            int r1 = ps1.executeUpdate();
            int r2 = ps2.executeUpdate();
            System.out.println(r1+"\n"+r2);
            ps2.close();
            ps1.close();
            conn.close();
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }
    }
    //6.通过JDBC查询显示所有员工信息和所属部门信息
    public static void f(){
        try {
            Class.forName(Driver.class.getName());
        }catch (ClassNotFoundException e){
            System.out.println("没有找到驱动！");
        }
        try {
            String sul = "jdbc:mysql://localhost:3306/bd2005?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
            String username = "root";
            String password = "a123456";
            Connection conn = DriverManager.getConnection(sul,username,password);
            String sql = "select *from emp,dept where emp.deptno=dept.deptno";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String job = rs.getString(3);
                int mgr = rs.getInt(4);
                String hiredate = rs.getString(5);
                int sal = rs.getInt(6);
                int comm = rs.getInt(7);
                int deptno = rs.getInt(8);
                int state = rs.getInt(9);
                int deptno1 = rs.getInt(10);
                String dname = rs.getString(11);
                String loc = rs.getString(12);
                System.out.print(id+"\t");
                System.out.print(name +"\t");
                System.out.print(job+"\t");
                System.out.print(mgr+"\t");
                System.out.print(hiredate+"\t");
                System.out.print(sal+"\t");
                System.out.print(comm+"\t");
                System.out.print(deptno+"\t");
                System.out.print(state+"\t");
                System.out.print(deptno1+"\t");
                System.out.print(dname+"\t");
                System.out.print(loc+"\n");
            }
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }
    }
    //7.通过JDBC查询指定用户信息（模糊姓名查询）
    public static void g(){
        Scanner in = new Scanner(System.in);
        System.out.println("请输入关键字");
        String name = in.next();
        try {
            Class.forName(Driver.class.getName());
        }catch (ClassNotFoundException e){
            System.out.println("没有找到驱动！");
        }
        try {
            String sul = "jdbc:mysql://localhost:3306/bd2005?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
            String username = "root";
            String password = "a123456";
            Connection conn = DriverManager.getConnection(sul,username,password);
            String sql = "select * from emp where ename like ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,"%"+name+"%");
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString(3);
                int mgr = rs.getInt(4);
                String hiredate = rs.getString(5);
                int sal = rs.getInt(6);
                int comm = rs.getInt(7);
                int deptno = rs.getInt(8);
                int state = rs.getInt(9);
                System.out.print(id+"\t");
                System.out.print(ename+"\t");
                System.out.print(job+"\t");
                System.out.print(mgr+"\t");
                System.out.print(hiredate+"\t");
                System.out.print(sal+"\t");
                System.out.print(comm+"\t");
                System.out.print(deptno+"\t");
                System.out.print(state+"\n");
            }
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }
    }
    public static void main(String[] args) {
        d();
    }
}
